package com.kitri.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.kitri.db.SetOfDataSource;
import com.kitri.db.Util;

import com.kitri.domain.Member;

public class MemberDaoImpl implements MemberDao {

	@Override
	public boolean insert(Member member) {
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO MEMBER (");
		sql.append("seq_id, ");
		sql.append("member_id, ");
		sql.append("member_password, ");
		sql.append("name, ");
		sql.append("gender, ");
		sql.append("date_of_birth, ");
		sql.append("phone_number, ");
		sql.append("email, ");
		sql.append("university, ");
		sql.append("major, ");
		sql.append("graduated_year, ");
		sql.append("address, ");
		sql.append("add_point, ");
		sql.append("language");

		if (member.getCourse() != null) {
			sql.append(", course_id");
		}
		sql.append(") VALUES(");
		sql.append("concat(substr(?, 1, 8-length(to_char(member_seq.nextval))), to_char(member_seq.nextval)), ");
		sql.append("?, ?, ?, ?, to_date(?, 'YYYY-MM-DD'), ?, ?, ?, ?, to_date(?, 'YYYY'), ?, ?, ?");

		if (member.getCourse() != null) {
			sql.append(", ?");
		}
		sql.append(")");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = 0;

		if (member.getCourse() == null) {
			result = jdbcTemplate.update(
					sql.toString(),
					new Object[] { member.getSeqId(), member.getMemberId(),
							member.getMemberPassword(), member.getName(),
							member.getGender(), member.getDateOfBirth(),
							member.getPhoneNumber(), member.getEmail(),
							member.getUniversity(), member.getMajor(),
							member.getGraduatedYear(), member.getAddress(),
							member.getAddPoint(), member.getLanguage() });
		} else {
			result = jdbcTemplate.update(
					sql.toString(),
					new Object[] { member.getSeqId(), member.getMemberId(),
							member.getMemberPassword(), member.getName(),
							member.getGender(), member.getDateOfBirth(),
							member.getPhoneNumber(), member.getEmail(),
							member.getUniversity(), member.getMajor(),
							member.getGraduatedYear(), member.getAddress(),
							member.getAddPoint(), member.getLanguage(),
							member.getCourse().getCourseId() });
		}

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public boolean update(Member member) {
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE MEMBER SET ");
		sql.append("member_password = ?");
		sql.append(", phone_number = ?");
		sql.append(", email = ?");
		sql.append(", university = ?");
		sql.append(", major = ?");
		sql.append(", graduated_year = to_date(?, 'YYYY')");
		sql.append(", address = ?");
		sql.append(", add_point = ?");
		sql.append(", language = ?");
		if (member.getCourse() != null) {
			sql.append(", course_id = ? ");
		}
		sql.append("WHERE seq_id = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = 0;

		if (member.getCourse() == null) {
			result = jdbcTemplate.update(
					sql.toString(),
					new Object[] { member.getMemberPassword(),
							member.getPhoneNumber(), member.getEmail(),
							member.getUniversity(), member.getMajor(),
							member.getGraduatedYear(), member.getAddress(),
							member.getAddPoint(), member.getLanguage(),
							member.getSeqId() });
		} else {
			result = jdbcTemplate
					.update(sql.toString(),
							new Object[] { member.getMemberPassword(),
									member.getName(), member.getGender(),
									member.getDateOfBirth(),
									member.getPhoneNumber(), member.getEmail(),
									member.getUniversity(), member.getMajor(),
									member.getGraduatedYear(),
									member.getAddress(), member.getAddPoint(),
									member.getLanguage(),
									member.getCourse().getCourseId(),
									member.getSeqId() });
		}

		if (result > 0)
			return true;
		return false;
	}

	public void updateCourse(String seqId, String courseId) {
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE MEMBER SET ");
		sql.append("course_id = ? ");
		sql.append("WHERE seq_id = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		jdbcTemplate.update(sql.toString(), new Object[] {
				courseId, seqId });
	}

	@Override
	public List<Member> selectAll(int num) {
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat gsf = new SimpleDateFormat("yyyy");
		StringBuffer sql = new StringBuffer();
		sql.append("select * from member");

		switch (num) {
		case 1:
			sql.append(" where seq_id like 'E%'");
			break;
		case 2:
			sql.append(" where seq_id like 'T%'");
			break;
		case 3:
			sql.append(" where seq_id like 'S%'");
			break;
		default:
			break;
		}
		sql.append(" order by seq_id, name");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		List<Member> list = new ArrayList<Member>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql
				.toString());

		for (Map<String, Object> row : rows) {
			Member member = new Member();

			member.setSeqId((String) row.get("seq_id"));
			member.setMemberId((String) row.get("member_id"));
			member.setMemberPassword((String) row.get("member_password"));
			member.setName((String) row.get("name"));
			member.setGender((String) row.get("gender"));
			member.setDateOfBirth(sf.format((Date) row.get("date_of_birth")));
			member.setPhoneNumber((String) row.get("phone_number"));
			member.setEmail((String) row.get("email"));
			member.setUniversity((String) row.get("university"));
			member.setMajor((String) row.get("major"));
			if (row.get("graduated_year") != null) {
				member.setGraduatedYear(gsf.format((Date) row
						.get("graduated_year")));
			}
			member.setAddress((String) row.get("address"));
			member.setAddPoint((String) row.get("add_point"));
			member.setLanguage((String) row.get("language"));
			member.setCourse(((CourseDao) AppContext.getAppContext().getBean(
					"courseDao")).select((String) row.get("course_id")));
			member.setCertificationList(((CertificationDaoImpl) AppContext
					.getAppContext().getBean("certificationDao"))
					.selectOfCertification((String) row.get("seq_id")));

			list.add(member);
		}

		return list;
	}

	@Override
	public boolean delete(String seqId) {
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM MEMBER ");
		sql.append("WHERE seq_id = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = jdbcTemplate
				.update(sql.toString(), new Object[] { seqId });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public Member select(String seqId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM MEMBER ");
		sql.append("WHERE SEQ_ID = ?");

		final Member member = new Member();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		jdbcTemplate.query(sql.toString(), new Object[] { seqId },
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						member.setSeqId(rs.getString("seq_id"));
						member.setMemberId(rs.getString("member_id"));
						member.setMemberPassword(rs
								.getString("member_password"));
						member.setName(rs.getString("name"));
						member.setGender(rs.getString("gender"));
						member.setDateOfBirth(rs.getString("date_of_birth")
								.substring(0, 10));
						member.setPhoneNumber(rs.getString("phone_number"));
						member.setEmail(rs.getString("email"));
						member.setUniversity(rs.getString("university"));
						member.setMajor(rs.getString("major"));
						if (rs.getString("graduated_year") != null) {
							member.setGraduatedYear(rs.getString(
									"graduated_year").substring(0, 4));
						}
						member.setAddress(rs.getString("address"));
						member.setAddPoint(rs.getString("add_point"));
						member.setLanguage(rs.getString("language"));
						member.setCourse(((CourseDao) AppContext
								.getAppContext().getBean("courseDao"))
								.select(rs.getString("course_id")));
						member.setCertificationList(((CertificationDaoImpl) AppContext
								.getAppContext().getBean("certificationDao"))
								.selectOfCertification(rs.getString("seq_id")));
					}
				});

		return member;
	}

	@Override
	public Member selectOfId(String memberId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM MEMBER ");
		sql.append("WHERE MEMBER_ID = ?");

		final Member member = new Member();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		jdbcTemplate.query(sql.toString(), new Object[] { memberId },
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						member.setSeqId(rs.getString("seq_id"));
						member.setMemberId(rs.getString("member_id"));
						member.setMemberPassword(rs
								.getString("member_password"));
						member.setName(rs.getString("name"));
						member.setGender(rs.getString("gender"));
						member.setDateOfBirth(rs.getString("date_of_birth")
								.substring(0, 10));
						member.setPhoneNumber(rs.getString("phone_number"));
						member.setEmail(rs.getString("email"));
						member.setUniversity(rs.getString("university"));
						member.setMajor(rs.getString("major"));
						if (rs.getString("graduated_year") != null) {
							member.setGraduatedYear(rs.getString(
									"graduated_year").substring(0, 4));
						}
						member.setAddress(rs.getString("address"));
						member.setAddPoint(rs.getString("add_point"));
						member.setLanguage(rs.getString("language"));
						member.setCourse(((CourseDao) AppContext
								.getAppContext().getBean("courseDao"))
								.select(rs.getString("course_id")));
						member.setCertificationList(((CertificationDaoImpl) AppContext
								.getAppContext().getBean("certificationDao"))
								.selectOfCertification(rs.getString("seq_id")));
					}
				});

		return member;
	}

	@Override
	public Member loginId(String userName, String password) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM MEMBER ");
		sql.append("WHERE member_id = ? ");
		sql.append("AND member_password = ?");

		final Member member = new Member();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		jdbcTemplate.query(sql.toString(), new Object[] { userName, password },
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						member.setSeqId(rs.getString("seq_id"));
						member.setMemberId(rs.getString("member_id"));
						member.setMemberPassword(rs
								.getString("member_password"));
						member.setName(rs.getString("name"));
						member.setGender(rs.getString("gender"));
						member.setDateOfBirth(rs.getString("date_of_birth")
								.substring(0, 10));
						member.setPhoneNumber(rs.getString("phone_number"));
						member.setEmail(rs.getString("email"));
						member.setUniversity(rs.getString("university"));
						member.setMajor(rs.getString("major"));
						if (rs.getString("graduated_year") != null) {
							member.setGraduatedYear(rs.getString(
									"graduated_year").substring(0, 4));
						}
						member.setAddress(rs.getString("address"));
						member.setAddPoint(rs.getString("add_point"));
						member.setLanguage(rs.getString("language"));
						member.setCourse(((CourseDao) AppContext
								.getAppContext().getBean("courseDao"))
								.select(rs.getString("course_id")));
						member.setCertificationList(((CertificationDaoImpl) AppContext
								.getAppContext().getBean("certificationDao"))
								.selectOfCertification(rs.getString("seq_id")));
					}
				});

		if (member.getSeqId() != null)
			return member;
		return null;
	}

	@Override
	public boolean idCheck(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM MEMBER ");
		sql.append("WHERE member_id = ? ");

		final Member member = new Member();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		jdbcTemplate.query(sql.toString(), new Object[] { id },
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						member.setSeqId(rs.getString("seq_id"));
					}
				});

		if (member.getSeqId() == null)
			return true;
		return false;
	}

	@Override
	public String findOfId(String name, String email) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM MEMBER ");
		sql.append("WHERE name = ? ");
		sql.append("AND email = ?");

		final Member member = new Member();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		jdbcTemplate.query(sql.toString(), new Object[] { name, email },
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						member.setMemberId(rs.getString("member_id"));
					}
				});

		return member.getMemberId();
	}

	@Override
	public String findOfPassword(String id, String birthDay, String email) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM MEMBER ");
		sql.append("WHERE member_id = ? ");
		sql.append("AND date_of_birth = to_date(?, 'YYYY-MM-DD') ");
		sql.append("AND email = ?");

		final Member member = new Member();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		jdbcTemplate.query(sql.toString(),
				new Object[] { id, birthDay, email }, new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						member.setMemberPassword(rs
								.getString("member_password"));
					}
				});

		return member.getMemberPassword();
	}
}
